package com.serviceprovider.business.module.admin.faq.services.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.serviceprovider.business.module.admin.faq.model.Answer;
import com.serviceprovider.business.module.admin.faq.model.Category;
import com.serviceprovider.business.module.admin.faq.model.Faq;
import com.serviceprovider.business.module.admin.faq.model.Question;
import com.serviceprovider.business.module.admin.faq.services.dao.interfaces.IFaqService;
import com.serviceprovider.business.module.common.Page;
import com.serviceprovider.business.module.common.PaginationHelper;


import org.springframework.stereotype.Service;

import sun.security.krb5.Asn1Exception;

@Service("faqService")
public class FaqServiceDaoImpl extends JdbcDaoSupport implements IFaqService {

	@Autowired
	public FaqServiceDaoImpl(
			@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
		setJdbcTemplate(jdbcTemplate);
	}

	private static final String TABLE_CATEGORY = "question_category";
	private static final String F_CATEGORY_ID = "category_id";
	private static final String F_CATEGORY_NAME = "name";

	private static final String TABLE_QUESTION = "question";
	private static final String F_QUESTION_ID = "question_id";
	private static final String F_EMAIL = "email";
	private static final String F_FIRST_NAME = "first_name";
	private static final String F_LAST_NAME = "last_name";
	private static final String F_QUESTION = "question";
	private static final String F_CONFIDENTIAL = "confidential";
	private static final String F_ORDER_NUMBER = "order_number";
	private static final String F_USER_ID = "user_id";
	private static final String F_REF_Num = "ref_number";
	private static final String F_IS_FAQ = "isFaq";

	private static final String TABLE_ANSWER = "answer";
	private static final String F_ANSWER_ID = "answer_id";
	private static final String F_SUBJECT = "subject";
	private static final String F_ANSWER = "answer";
	private static final String F_RATING = "rating";
	private static final String F_RATE_COUNT = "rate_count";
	private static final String F_TOP_RANK = "top_rank";

	private static final String TABLE_FAQ = "faq";
	private static final String F_FAQ_ID = "faq_id";

	private static final String F_TAG = "tag";
	private static final String F_CREATED = "created";
	private static final String F_UPDATED = "updated";

	private static final String F_QUESTION_ALIAS = "q";
	private static final String F_ANSWER_ALIAS = "a";
	private static final String F_CATEGORY_ALIAS = "c";

	private static final String INSERT_CATEGORY = new StringBuffer()
			.append("INSERT INTO ").append(TABLE_CATEGORY).append("(")
			.append(F_CATEGORY_NAME).append(") VALUES (?)").toString();

	private static final String UPDATE_CATEGORY = new StringBuffer()
			.append("UPDATE ").append(TABLE_CATEGORY).append(" SET ")
			.append(F_CATEGORY_NAME).append(" = ? WHERE ")
			.append(F_CATEGORY_ID).append(" = ?").toString();

	private static final String DELETE_CATEGORY = new StringBuffer()
			.append("DELETE FROM ").append(TABLE_CATEGORY).append(" WHERE ")
			.append(F_CATEGORY_ID).append(" = ?").toString();

	private static final String GET_CATEGORY = new StringBuffer()
			.append("SELECT * FROM ").append(TABLE_CATEGORY).append(" WHERE ")
			.append(F_CATEGORY_ID).append(" = ?").toString();

	private static final String LIST_CATEGORY = new StringBuffer()
			.append("SELECT * FROM ").append(TABLE_CATEGORY).toString();

	private static final String INSERT_QUESTION = new StringBuffer()
			.append("INSERT INTO ").append(TABLE_QUESTION).append("(")
			.append(F_EMAIL).append(",").append(F_FIRST_NAME).append(",")
			.append(F_LAST_NAME).append(",").append(F_USER_ID).append(",")
			.append(F_CATEGORY_ID).append(",").append(F_QUESTION).append(",")
			.append(F_ORDER_NUMBER).append(",").append(F_TAG).append(",")
			.append(F_CONFIDENTIAL).append(",").append(F_CREATED).append(",")
			.append(F_UPDATED).append(",").append(F_REF_Num)
			.append(") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)").toString();

	private static final String UPDATE_QUESTION = new StringBuffer()
			.append("UPDATE ").append(TABLE_QUESTION).append(" SET ")
			.append(F_EMAIL).append(" = ?, ").append(F_FIRST_NAME)
			.append(" = ?, ").append(F_LAST_NAME).append(" = ?, ")
			.append(F_CATEGORY_ID).append(" = ?, ").append(F_QUESTION)
			.append(" = ?, ").append(F_ORDER_NUMBER).append(" = ?, ")
			.append(F_TAG).append(" = ?, ").append(F_CONFIDENTIAL)
			.append(" = ?, ").append(F_UPDATED).append(" = ? ")
			.append("WHERE ").append(F_QUESTION_ID).append(" = ?").toString();
	
	private static final String UPDATE_QUESTION_FAQ = new StringBuffer()
			.append("UPDATE ").append(TABLE_QUESTION).append(" SET ")
			.append(F_IS_FAQ).append(" = ? ").append("WHERE ")
			.append(F_QUESTION_ID).append(" = ?").toString();
	
	private static final String RANK_QUESTION = new StringBuffer()
			.append("UPDATE ").append(TABLE_QUESTION).append(" SET ")
			.append(F_TOP_RANK).append(" = ? ").append("WHERE ")
			.append(F_QUESTION_ID).append(" = ?").toString();

	private static final String DELETE_QUESTION = new StringBuffer()
			.append("DELETE FROM ").append(TABLE_QUESTION).append(" WHERE ")
			.append(F_QUESTION_ID).append(" = ?").toString();

	private static final String GET_QUESTION = new StringBuffer()
			.append("SELECT " + F_QUESTION_ALIAS + ".*, " + F_CATEGORY_ALIAS
					+ ".*, '0' AS HAS_ANSWER FROM ")
			.append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY)
			.append(" " + F_CATEGORY_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND " + F_QUESTION_ALIAS + ".")
			.append(F_QUESTION_ID).append(" = ?").toString();

	private static final String COUNT_ANSWER = new StringBuffer()
			.append("(SELECT COUNT(*) FROM ").append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + " WHERE " + F_ANSWER_ALIAS + ".")
			.append(F_QUESTION_ID).append(" = " + F_QUESTION_ALIAS + ".")
			.append(F_QUESTION_ID).append(") AS HAS_ANSWER").toString();

	private static final String COUNT_ANSWER_2 = new StringBuffer()
			.append("(SELECT COUNT(*) FROM ").append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + " WHERE " + F_ANSWER_ALIAS + ".")
			.append(F_QUESTION_ID).append(" = " + F_QUESTION_ALIAS + ".")
			.append(F_QUESTION_ID).append(")").toString();

	private static final String COUNT_LIST_QUESTION = new StringBuffer()
			.append("SELECT COUNT(*) FROM ").append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + " WHERE ")
			.append(F_QUESTION_ALIAS + ".").append(F_REF_Num).append(" like ?")
			.toString();

	private static final String COUNT_LIST_QUESTION_BY_USER = new StringBuffer()
			.append("SELECT COUNT(*) FROM ").append(TABLE_QUESTION)
			.append(" WHERE ").append(F_USER_ID).append(" = ?").toString();

	private static final String LIST_QUESTION = new StringBuffer()
			.append("SELECT " + F_QUESTION_ALIAS + ".*, LEFT("
					+ F_QUESTION_ALIAS + ".")
			.append(F_QUESTION)
			.append(", 50) AS QUESTION_")
			.append(", " + F_CATEGORY_ALIAS + ".*, ")
			.append(COUNT_ANSWER)
			.append(" FROM ")
			.append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY)
			.append(" " + F_CATEGORY_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND " + F_QUESTION_ALIAS + ".")
			.append(F_REF_Num).append(" like ?").toString();

	private static final String LIST_QUESTION_BY_USER = new StringBuffer()
			.append("SELECT " + F_QUESTION_ALIAS + ".*, LEFT("
					+ F_QUESTION_ALIAS + ".")
			.append(F_QUESTION)
			.append(", 50) AS QUESTION_")
			.append(", " + F_CATEGORY_ALIAS + ".*, ")
			.append(COUNT_ANSWER)
			.append(" FROM ")
			.append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY)
			.append(" " + F_CATEGORY_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND " + F_QUESTION_ALIAS + ".")
			.append(F_USER_ID).append(" = ?").toString();

	private static final String COUNT_LIST_REPLIED_QUESTION = new StringBuffer()
			.append("SELECT COUNT(*) FROM ").append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + ", ").append(TABLE_CATEGORY)
			.append(" " + F_CATEGORY_ALIAS + ",").append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND " + F_ANSWER_ALIAS + ".")
			.append(F_QUESTION_ID).append(" = " + F_QUESTION_ALIAS + ".")
			.append(F_QUESTION_ID).append(" AND " + F_QUESTION_ALIAS + ".")
			.append(F_REF_Num).append(" like ?").toString();

	private static final String LIST_REPLIED_QUESTION = new StringBuffer()
			.append("SELECT " + F_QUESTION_ALIAS + ".*, LEFT("
					+ F_QUESTION_ALIAS + ".").append(F_QUESTION)
			.append(", 50) AS QUESTION_")
			.append(", " + F_CATEGORY_ALIAS + ".*, '1' AS HAS_ANSWER FROM ")
			.append(TABLE_QUESTION).append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY).append(" " + F_CATEGORY_ALIAS + ",")
			.append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND " + F_ANSWER_ALIAS + ".")
			.append(F_QUESTION_ID).append(" = " + F_QUESTION_ALIAS + ".")
			.append(F_QUESTION_ID).append(" AND " + F_QUESTION_ALIAS + ".")
			.append(F_REF_Num).append(" like ?").toString();

	private static final String COUNT_LIST_REPLIED_QUESTION_BY_CATEGORY = new StringBuffer()
			.append("SELECT COUNT(*) FROM ").append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + ", ").append(TABLE_CATEGORY)
			.append(" " + F_CATEGORY_ALIAS + ",").append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND " + F_ANSWER_ALIAS + ".")
			.append(F_QUESTION_ID).append(" = " + F_QUESTION_ALIAS + ".")
			.append(F_QUESTION_ID).append(" AND " + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" = ?")
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_REF_Num)
			.append(" like ?").toString();

	private static final String LIST_REPLIED_QUESTION_BY_CATEGORY = new StringBuffer()
			.append("SELECT " + F_QUESTION_ALIAS + ".*, LEFT("
					+ F_QUESTION_ALIAS + ".").append(F_QUESTION)
			.append(", 50) AS QUESTION_")
			.append(", " + F_CATEGORY_ALIAS + ".*, '1' AS HAS_ANSWER FROM ")
			.append(TABLE_QUESTION).append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY).append(" " + F_CATEGORY_ALIAS + ",")
			.append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND " + F_ANSWER_ALIAS + ".")
			.append(F_QUESTION_ID).append(" = " + F_QUESTION_ALIAS + ".")
			.append(F_QUESTION_ID).append(" AND " + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" = ?")
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_REF_Num)
			.append(" like ?").toString();

	private static final String COUNT_LIST_UNANSWERED_QUESTION = new StringBuffer()
			.append("SELECT COUNT(*) FROM ")
			.append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY)
			.append(" " + F_CATEGORY_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND ").append(COUNT_ANSWER_2)
			.append(" = 0").append(" AND " + F_QUESTION_ALIAS + ".")
			.append(F_REF_Num).append(" like ?").toString();

	private static final String LIST_UNANSWERED_QUESTION = new StringBuffer()
			.append("SELECT " + F_QUESTION_ALIAS + ".*, LEFT("
					+ F_QUESTION_ALIAS + ".").append(F_QUESTION)
			.append(", 50) AS QUESTION_")
			.append(", " + F_CATEGORY_ALIAS + ".*, '0' AS HAS_ANSWER FROM ")
			.append(TABLE_QUESTION).append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY)
			.append(" c WHERE " + F_QUESTION_ALIAS + ".").append(F_CATEGORY_ID)
			.append("=" + F_CATEGORY_ALIAS + ".").append(F_CATEGORY_ID)
			.append(" AND ").append(COUNT_ANSWER_2).append(" = 0")
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_REF_Num)
			.append(" like ?").toString();

	private static final String COUNT_LIST_UNANSWERED_QUESTION_BY_CATEGORY = new StringBuffer()
			.append("SELECT COUNT(*) FROM ")
			.append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY)
			.append(" " + F_CATEGORY_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND ").append(COUNT_ANSWER_2)
			.append(" = 0").append(" AND " + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" = ?")
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_REF_Num)
			.append(" like ?").toString();

	private static final String LIST_UNANSWERED_QUESTION_BY_CATEGORY = new StringBuffer()
			.append("SELECT " + F_QUESTION_ALIAS + ".*, LEFT("
					+ F_QUESTION_ALIAS + ".")
			.append(F_QUESTION)
			.append(", 50) AS QUESTION_")
			.append(", " + F_CATEGORY_ALIAS + ".*, '0' AS HAS_ANSWER FROM ")
			.append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY)
			.append(" " + F_CATEGORY_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND ").append(COUNT_ANSWER_2)
			.append(" = 0").append(" AND " + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" = ?")
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_REF_Num)
			.append(" like ?").toString();

	private static final String COUNT_LIST_QUESTION_BY_CATEGORY = new StringBuffer()
			.append("SELECT COUNT(*) FROM ")
			.append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY)
			.append(" " + F_CATEGORY_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND " + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" = ?")
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_REF_Num)
			.append(" like ?").toString();

	private static final String LIST_QUESTION_BY_CATEGORY = new StringBuffer()
			.append("SELECT " + F_QUESTION_ALIAS + ".*, LEFT("
					+ F_QUESTION_ALIAS + ".")
			.append(F_QUESTION)
			.append(", 50) AS QUESTION_")
			.append(", " + F_CATEGORY_ALIAS + ".*, ")
			.append(COUNT_ANSWER)
			.append(" FROM ")
			.append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY)
			.append(" " + F_CATEGORY_ALIAS + " WHERE " + F_QUESTION_ALIAS + ".")
			.append(F_CATEGORY_ID).append("=" + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" AND " + F_CATEGORY_ALIAS + ".")
			.append(F_CATEGORY_ID).append(" = ?")
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_REF_Num)
			.append(" like ?").toString();

	private static final String INSERT_ANSWER = new StringBuffer()
			.append("INSERT INTO ").append(TABLE_ANSWER).append("(")
			.append(F_QUESTION_ID).append(",").append(F_ANSWER).append(",")
			.append(F_TAG).append(",").append(F_CREATED).append(",")
			.append(F_UPDATED).append(") VALUES (?, ?, ?, ?, ?)").toString();

	private static final String UPDATE_ANSWER = new StringBuffer()
			.append("UPDATE ").append(TABLE_ANSWER).append(" SET ")
			.append(F_QUESTION_ID).append(" = ?,").append(F_ANSWER)
			.append(" = ?,").append(F_TAG).append(" = ?,").append(F_UPDATED)
			.append(" = ? ").append("WHERE ").append(F_ANSWER_ID)
			.append(" = ?").toString();

	private static final String RATE_ANSWER = new StringBuffer()
			.append("UPDATE ").append(TABLE_ANSWER).append(" SET ")
			.append(F_RATING).append("=").append(F_RATING).append("+?, ")
			.append(F_RATE_COUNT).append("=").append(F_RATE_COUNT).append("+1")
			.append(" WHERE ").append(F_ANSWER_ID).append(" = ?").toString();

	private static final String DELETE_ANSWER = new StringBuffer()
			.append("DELETE FROM ").append(TABLE_ANSWER).append(" WHERE ")
			.append(F_ANSWER_ID).append(" = ?").toString();

	private static final String GET_ANSWER = new StringBuffer()
			.append("SELECT " + F_ANSWER_ALIAS + ".*, " + F_QUESTION_ALIAS
					+ ".* FROM ").append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + ", ").append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + "")
			.append(" WHERE " + F_ANSWER_ALIAS + ".").append(F_QUESTION_ID)
			.append(" = " + F_QUESTION_ALIAS + ".").append(F_QUESTION_ID)
			.append(" AND ").append(F_ANSWER_ID).append(" = ?").toString();

	private static final String GET_ANSWER_FOR_QUESTION = new StringBuffer()
			.append("SELECT " + F_ANSWER_ALIAS + ".*, " + F_QUESTION_ALIAS
					+ ".* FROM ").append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + ", ").append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + "")
			.append(" WHERE " + F_ANSWER_ALIAS + ".").append(F_QUESTION_ID)
			.append(" = " + F_QUESTION_ALIAS + ".").append(F_QUESTION_ID)
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_QUESTION_ID)
			.append(" = ?").toString();

	private static final String LIST_ANSWER = new StringBuffer()
			.append("SELECT " + F_ANSWER_ALIAS + ".*, " + F_QUESTION_ALIAS
					+ ".* FROM ").append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + ", ").append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + "")
			.append(" WHERE " + F_ANSWER_ALIAS + ".").append(F_QUESTION_ID)
			.append(" = " + F_QUESTION_ALIAS + ".").append(F_QUESTION_ID)
			.append(" ORDER BY ").append(F_UPDATED).append(" DESC").toString();

	private static final String COUNT_FIND_ANSWERS = new StringBuffer()
			.append("SELECT COUNT(*) FROM ").append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + ", ").append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + " ")
			.append(" WHERE " + F_ANSWER_ALIAS + ".").append(F_QUESTION_ID)
			.append(" = " + F_QUESTION_ALIAS + ".").append(F_QUESTION_ID)
			.append(" AND (" + F_ANSWER_ALIAS + ".").append(F_ANSWER)
			.append(" LIKE ?").append(" OR " + F_QUESTION_ALIAS + ".")
			.append(F_QUESTION).append(" LIKE ?")
			.append(" OR " + F_ANSWER_ALIAS + ".").append(F_TAG)
			.append(" LIKE ?").append(" OR " + F_QUESTION_ALIAS + ".")
			.append(F_TAG).append(" LIKE ?")
			.append(") AND " + F_QUESTION_ALIAS + ".").append(F_CONFIDENTIAL)
			.append(" = 0").toString();

	private static final String FIND_ANSWERS = new StringBuffer()
			.append("SELECT " + F_ANSWER_ALIAS + ".*, " + F_QUESTION_ALIAS
					+ ".* FROM ").append(TABLE_ANSWER)
			.append(" " + F_ANSWER_ALIAS + ", ").append(TABLE_QUESTION)
			.append(" " + F_QUESTION_ALIAS + " ")
			.append(" WHERE " + F_ANSWER_ALIAS + ".").append(F_QUESTION_ID)
			.append(" = " + F_QUESTION_ALIAS + ".").append(F_QUESTION_ID)
			.append(" AND (" + F_ANSWER_ALIAS + ".").append(F_ANSWER)
			.append(" LIKE ?").append(" OR " + F_QUESTION_ALIAS + ".")
			.append(F_QUESTION).append(" LIKE ?")
			.append(" OR " + F_ANSWER_ALIAS + ".").append(F_TAG)
			.append(" LIKE ?").append(" OR " + F_QUESTION_ALIAS + ".")
			.append(F_TAG).append(" LIKE ?")
			.append(") AND " + F_QUESTION_ALIAS + ".").append(F_CONFIDENTIAL)
			.append(" = 0").toString();

	private static final String LIST_TOP_TEN_ANSWERS = new StringBuffer()
			.append("SELECT " + F_ANSWER_ALIAS + ".*, " + F_QUESTION_ALIAS
					+ ".*, " + F_CATEGORY_ALIAS + ".* FROM ")
			.append(TABLE_ANSWER).append(" " + F_ANSWER_ALIAS + ", ")
			.append(TABLE_QUESTION).append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY).append(" " + F_CATEGORY_ALIAS + "")
			.append(" WHERE " + F_ANSWER_ALIAS + ".").append(F_QUESTION_ID)
			.append(" = " + F_QUESTION_ALIAS + ".").append(F_QUESTION_ID)
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_CATEGORY_ID)
			.append(" = " + F_CATEGORY_ALIAS + ".").append(F_CATEGORY_ID)
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_CONFIDENTIAL)
			.append(" = 0").append(" ORDER BY " + F_QUESTION_ALIAS + ".")
			.append(F_TOP_RANK).append(" DESC").append(" LIMIT 0, 10")
			.toString();

	private static final String LIST_OTHER_ANSWERS = new StringBuffer()
			.append("SELECT " + F_ANSWER_ALIAS + ".*, " + F_QUESTION_ALIAS
					+ ".*, " + F_CATEGORY_ALIAS + ".* FROM ")
			.append(TABLE_ANSWER).append(" " + F_ANSWER_ALIAS + ", ")
			.append(TABLE_QUESTION).append(" " + F_QUESTION_ALIAS + ", ")
			.append(TABLE_CATEGORY).append(" " + F_CATEGORY_ALIAS + "")
			.append(" WHERE " + F_ANSWER_ALIAS + ".").append(F_QUESTION_ID)
			.append(" = " + F_QUESTION_ALIAS + ".").append(F_QUESTION_ID)
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_CATEGORY_ID)
			.append(" = " + F_CATEGORY_ALIAS + ".").append(F_CATEGORY_ID)
			.append(" AND " + F_QUESTION_ALIAS + ".").append(F_CONFIDENTIAL)
			.append(" = 0").append(" AND " + F_ANSWER_ALIAS + ".")
			.append(F_ANSWER_ID).append(" <> ?")
			.append(" ORDER BY " + F_QUESTION_ALIAS + ".").append(F_TOP_RANK)
			.append(" DESC").append(" LIMIT 0, 4").toString();

	private static final String INSERT_FAQ = new StringBuffer()
			.append("INSERT INTO ").append(TABLE_FAQ).append("(")
			.append(F_QUESTION).append(",").append(F_ANSWER).append(",")
			.append(F_TAG).append(",").append(F_CREATED).append(",")
			.append(F_UPDATED).append(") VALUES (?, ?, ?, ?, ?)").toString();

	private static final String UPDATE_FAQ = new StringBuffer()
			.append("UPDATE ").append(TABLE_FAQ).append(" SET ")
			.append(F_QUESTION).append(" = ?,").append(F_ANSWER)
			.append(" = ?,").append(F_TAG).append(" = ?,").append(F_UPDATED)
			.append(" = ? ").append("WHERE ").append(F_FAQ_ID).append(" = ?")
			.toString();

	private static final String DELETE_FAQ = new StringBuffer()
			.append("DELETE FROM ").append(TABLE_FAQ).append(" WHERE ")
			.append(F_FAQ_ID).append(" = ?").toString();

	private static final String GET_FAQ = new StringBuffer()
			.append("SELECT * FROM ").append(TABLE_FAQ).append(" WHERE ")
			.append(F_FAQ_ID).append(" = ?").toString();

	private static final String COUNT_LIST_FAQ = new StringBuffer()
			.append("SELECT COUNT(*) FROM ").append(TABLE_FAQ)
			.append(" ORDER BY ").append(F_UPDATED).append(" DESC").toString();

	private static final String COUNT_LIST_CATEGORY = new StringBuffer()
			.append("SELECT COUNT(*) FROM ").append(TABLE_CATEGORY).toString();

	private static final String LIST_FAQ = new StringBuffer()
			.append("SELECT * FROM ").append(TABLE_FAQ).toString();

	@Override
	public void createAnswer(Answer answer) {
		getJdbcTemplate().update(
				INSERT_ANSWER,
				new Object[] { answer.getQuestionId(), answer.getAnswer(),
						answer.getTag(), answer.getCreated(),
						answer.getUpdated() });
	}

	@Override
	public void createQuestion(Question question) {
		getJdbcTemplate().update(
				INSERT_QUESTION,
				new Object[] { question.getEmail(), question.getFirstName(),
						question.getLastName(), question.getUserId(),
						question.getCategory().getId(), question.getQuestion(),
						question.getOrderNumber(), question.getTag(),
						question.isConfidential(), question.getCreated(),
						question.getUpdated(), question.getRefNumber() });
	}
	/***
	 This function to create Faq From Answer  
	 * 
	 * */
	@Override
	public void modifyQuestionFaq(int questionId,int isFaq)
	{
		
		getJdbcTemplate().update(UPDATE_QUESTION_FAQ,new Object[] {isFaq,questionId});
		
	}

	@Override
	public void deleteAnswer(int answerId) {
		getJdbcTemplate().update(DELETE_ANSWER, new Object[] { answerId });

	}

	@Override
	public void deleteQuestion(int questionId) {
		getJdbcTemplate().update(DELETE_QUESTION, new Object[] { questionId });

	}

	@Override
	public Page<Answer> findAnswers(String keyword, int pageNo, int recordSize,
			String sortColumn, String sortOrder) {
		PaginationHelper<Answer> pageHelper = new PaginationHelper<Answer>();
		ParameterizedRowMapper<Answer> mapper = new AnswerRowMapper();

		String likeKeyword = "%" + keyword + "%";

		return pageHelper.fetchPage(getJdbcTemplate(), COUNT_FIND_ANSWERS,
				FIND_ANSWERS + " order by a." + sortColumn + " " + sortOrder,
				new Object[] { likeKeyword, likeKeyword, likeKeyword,
						likeKeyword }, pageNo, recordSize, sortColumn,
				sortOrder, mapper);

	}

	@Override
	public Answer getAnswer(int answerId) {
		RowMapper mapper = new RowMapper() {
			@Override
			public Object mapRow(ResultSet result, int rowNum)
					throws SQLException {
				Answer answer = new Answer();
				answer.setId(result.getInt(F_ANSWER_ID));
				answer.setQuestionId(result.getInt(F_QUESTION_ID));
				answer.setQuestionSubject(result.getString(F_QUESTION));
				answer.setAnswer(result.getString(F_ANSWER));
				answer.setRating(result.getInt(F_RATING));
				answer.setCreated(result.getTimestamp(F_CREATED));
				answer.setUpdated(result.getTimestamp(F_UPDATED));
				answer.setRateCount(result.getInt(F_RATE_COUNT));
				return answer;
			}

		};
		try {
			return (Answer) getJdbcTemplate().queryForObject(GET_ANSWER,
					new Object[] { answerId }, mapper);
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}
	}

	@Override
	public Question getQuestion(int questionId) {
		try {
			return (Question) getJdbcTemplate().queryForObject(GET_QUESTION,
					new Object[] { questionId }, new QuestionRowMapper());
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}
	}

	@Override
	public List<Answer> listAnswers() {
		final List<Answer> listAnswers = new ArrayList<Answer>();

		getJdbcTemplate().query(LIST_ANSWER, new RowMapper() {

			@Override
			public Object mapRow(ResultSet result, int rowNum)
					throws SQLException {
				Answer answer = new Answer();
				answer.setId(result.getInt(F_ANSWER_ID));
				answer.setQuestionId(result.getInt(F_QUESTION_ID));

				String questionSubject = result.getString(F_SUBJECT);
				if (questionSubject != null && !questionSubject.equals("")
						&& questionSubject.length() > 30) {
					questionSubject = questionSubject.substring(0, 30).concat(
							"...");
				}
				answer.setQuestionSubject(questionSubject);

				String answerContent = result.getString(F_ANSWER);
				if (answerContent != null && !answerContent.equals("")
						&& answerContent.length() > 30) {
					answerContent = answerContent.substring(0, 30)
							.concat("...");
				}
				answer.setAnswer(answerContent);

				answer.setRating(result.getInt(F_RATING));
				answer.setCreated(result.getTimestamp(F_CREATED));
				answer.setUpdated(result.getTimestamp(F_UPDATED));
				listAnswers.add(answer);
				return null;
			}

		});

		return listAnswers;
	}

	@Override
	public Page<Question> listQuestions(int categoryId, String refNumber,
			int pageNo, int recordSize, String sortColumn, String sortOrder) {
		PaginationHelper<Question> pageHelper = new PaginationHelper<Question>();
		ParameterizedRowMapper<Question> mapper = new QuestionRowMapper(true);

		if (categoryId > 0) {

			return pageHelper.fetchPage(getJdbcTemplate(),
					COUNT_LIST_QUESTION_BY_CATEGORY, LIST_QUESTION_BY_CATEGORY
							+ " order by " + F_QUESTION_ALIAS + "."
							+ sortColumn + " " + sortOrder, new Object[] {
							categoryId, "%" + refNumber + "%" }, pageNo,
					recordSize, sortColumn, sortOrder, mapper);
		} else {
			return pageHelper.fetchPage(getJdbcTemplate(), COUNT_LIST_QUESTION,
					LIST_QUESTION + " order by " + F_QUESTION_ALIAS + "."
							+ sortColumn + " " + sortOrder, new Object[] { "%"
							+ refNumber + "%" }, pageNo, recordSize,
					sortColumn, sortOrder, mapper);
		}
	}

	@Override
	public Page<Question> listQuestionsByUser(int userId, int pageNo,
			int recordSize, String sortColumn, String sortOrder) {
		PaginationHelper<Question> pageHelper = new PaginationHelper<Question>();
		ParameterizedRowMapper<Question> mapper = new QuestionRowMapper(true);

		return pageHelper.fetchPage(getJdbcTemplate(),
				COUNT_LIST_QUESTION_BY_USER, LIST_QUESTION_BY_USER
						+ " order by " + F_QUESTION_ALIAS + "." + sortColumn
						+ " " + sortOrder, new Object[] { userId }, pageNo,
				recordSize, sortColumn, sortOrder, mapper);
	}

	@Override
	public void modifyAnswer(Answer answer) {
		getJdbcTemplate().update(
				UPDATE_ANSWER,
				new Object[] { answer.getQuestionId(), answer.getAnswer(),
						answer.getTag(), answer.getUpdated(), answer.getId() });
	}

	@Override
	public void modifyQuestion(Question question) {
		getJdbcTemplate().update(
				UPDATE_QUESTION,
				new Object[] { question.getEmail(), question.getFirstName(),
						question.getLastName(), question.getCategory().getId(),
						question.getQuestion(), question.getOrderNumber(),
						question.getCreated(), question.getUpdated(),
						question.getId() });

	}

	@Override
	public void createCategory(Category category) {
		getJdbcTemplate().update(INSERT_CATEGORY,
				new Object[] { category.getName() });
	}

	@Override
	public void deleteCategory(int categoryId) {
		getJdbcTemplate().update(DELETE_CATEGORY, new Object[] { categoryId });

	}

	@Override
	public Category getCategory(int categoryId) {
		RowMapper mapper = new RowMapper() {

			@Override
			public Object mapRow(ResultSet result, int rowNum)
					throws SQLException {
				Category cat = new Category();
				cat.setId(result.getInt(F_CATEGORY_ID));
				cat.setName(result.getString(F_CATEGORY_NAME));
				return cat;
			}

		};

		try {
			return (Category) getJdbcTemplate().queryForObject(GET_CATEGORY,
					new Object[] { categoryId }, mapper);
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}
	}

	@Override
	public List<Category> listCategories() {
		final List<Category> listCategories = new ArrayList<Category>();

		getJdbcTemplate().query(LIST_CATEGORY, new RowMapper() {

			@Override
			public Object mapRow(ResultSet result, int index)
					throws SQLException {
				Category cat = new Category();
				cat.setId(result.getInt(F_CATEGORY_ID));
				String catName = result.getString(F_CATEGORY_NAME);
				if (catName != null && catName.length() > 50) {
					catName = catName.substring(0, 50).concat("...");
				}
				cat.setName(catName);
				listCategories.add(cat);
				return null;
			}

		});

		return listCategories;
	}

	@Override
	public void modifyCategory(Category category) {
		getJdbcTemplate().update(UPDATE_CATEGORY,
				new Object[] { category.getName(), category.getId() });

	}

	@Override
	public List<Answer> listTopTenAnswers() {
		final List<Answer> listAnswers = new ArrayList<Answer>();

		getJdbcTemplate().query(LIST_TOP_TEN_ANSWERS, new RowMapper() {

			@Override
			public Object mapRow(ResultSet result, int rowNum)
					throws SQLException {
				Answer answer = new Answer();
				answer.setId(result.getInt(F_ANSWER_ID));
				answer.setQuestionId(result.getInt(F_QUESTION_ID));

				String questionSubject = result.getString(F_QUESTION);
				if (questionSubject != null && !questionSubject.equals("")
						&& questionSubject.length() > 50) {
					questionSubject = questionSubject.substring(0, 50).concat(
							"...");
				}
				answer.setQuestionSubject(questionSubject);

				String answerContent = result.getString(F_ANSWER);
				if (answerContent != null && !answerContent.equals("")
						&& answerContent.length() > 50) {
					answerContent = answerContent.substring(0, 50)
							.concat("...");
				}
				answer.setAnswer(answerContent);

				answer.setRating(result.getInt(F_RATING));
				answer.setCreated(result.getTimestamp(F_CREATED));
				answer.setUpdated(result.getTimestamp(F_UPDATED));
				listAnswers.add(answer);
				return null;
			}

		});

		return listAnswers;
	}

	@Override
	public List<Answer> listUsefulAnswers(int answerId) {
		final List<Answer> listAnswers = new ArrayList<Answer>();

		getJdbcTemplate().query(LIST_OTHER_ANSWERS, new Object[] { answerId },
				new RowMapper() {

					@Override
					public Object mapRow(ResultSet result, int rowNum)
							throws SQLException {
						Answer answer = new Answer();
						answer.setId(result.getInt(F_ANSWER_ID));
						answer.setQuestionId(result.getInt(F_QUESTION_ID));

						String questionSubject = result.getString(F_QUESTION);
						if (questionSubject != null
								&& !questionSubject.equals("")
								&& questionSubject.length() > 50) {
							questionSubject = questionSubject.substring(0, 50)
									.concat("...");
						}
						answer.setQuestionSubject(questionSubject);

						String answerContent = result.getString(F_ANSWER);
						if (answerContent != null && !answerContent.equals("")
								&& answerContent.length() > 50) {
							answerContent = answerContent.substring(0, 50)
									.concat("...");
						}
						answer.setAnswer(answerContent);

						answer.setRating(result.getInt(F_RATING));
						answer.setCreated(result.getTimestamp(F_CREATED));
						answer.setUpdated(result.getTimestamp(F_UPDATED));
						listAnswers.add(answer);
						return null;
					}

				});

		return listAnswers;
	}

	@Override
	public void createFaq(Faq faq) {
		getJdbcTemplate().update(
				INSERT_FAQ,
				new Object[] { faq.getQuestion(), faq.getAnswer(),
						faq.getTag(), faq.getCreated(), faq.getUpdated() });

	}

	@Override
	public void deleteFaq(int faqId) {
		getJdbcTemplate().update(DELETE_FAQ, new Object[] { faqId });
	}

	@Override
	public Page<Faq> listFaqs(int pageNo, int recordSize, String sortColumn,
			String sortOrder) {
		PaginationHelper<Faq> pageHelper = new PaginationHelper<Faq>();
		ParameterizedRowMapper<Faq> mapper = new FaqRowMapper();
		return pageHelper.fetchPage(getJdbcTemplate(), COUNT_LIST_FAQ, LIST_FAQ
				+ " order by " + sortColumn + " " + sortOrder, new Object[] {},
				pageNo, recordSize, sortColumn, sortOrder, mapper);
	}

	@Override
	public void modifyFaq(Faq faq) {
		getJdbcTemplate().update(
				UPDATE_FAQ,
				new Object[] { faq.getQuestion(), faq.getAnswer(),
						faq.getTag(), faq.getUpdated(), faq.getId() });
	}

	@Override
	public Faq getFaq(int faqId) {
		RowMapper mapper = new RowMapper() {

			@Override
			public Object mapRow(ResultSet result, int index)
					throws SQLException {
				Faq faq = new Faq();
				faq.setId(result.getInt(F_FAQ_ID));
				faq.setQuestion(result.getString(F_QUESTION));
				faq.setAnswer(result.getString(F_ANSWER));
				faq.setTag(result.getString(F_TAG));
				faq.setCreated(result.getTimestamp(F_CREATED));
				faq.setUpdated(result.getTimestamp(F_UPDATED));
				return faq;
			}

		};

		try {
			return (Faq) getJdbcTemplate().queryForObject(GET_FAQ,
					new Object[] { faqId }, mapper);
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}
	}

	@Override
	public Answer getAnswerForQuestion(int questionId) {
		RowMapper mapper = new RowMapper() {
			@Override
			public Object mapRow(ResultSet result, int rowNum)
					throws SQLException {
				Answer answer = new Answer();
				answer.setId(result.getInt(F_ANSWER_ID));
				answer.setQuestionId(result.getInt(F_QUESTION_ID));
				answer.setQuestionSubject(result.getString(F_QUESTION));
				answer.setAnswer(result.getString(F_ANSWER));
				answer.setTag(result.getString(F_TAG));
				answer.setRating(result.getInt(F_RATING));
				answer.setCreated(result.getTimestamp(F_CREATED));
				answer.setUpdated(result.getTimestamp(F_UPDATED));
				return answer;
			}

		};
		try {
			return (Answer) getJdbcTemplate().queryForObject(
					GET_ANSWER_FOR_QUESTION, new Object[] { questionId },
					mapper);
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}
	}

	@Override
	public Page<Question> listRepliedQuestions(int categoryId,
			String refNumber, int pageNo, int recordSize, String sortColumn,
			String sortOrder) {
		PaginationHelper<Question> pageHelper = new PaginationHelper<Question>();
		ParameterizedRowMapper<Question> mapper = new QuestionRowMapper(true);
		System.out.println("message3 " + COUNT_LIST_REPLIED_QUESTION);
		System.out.println("message4 " + LIST_REPLIED_QUESTION);
		System.out.println("message5 " + refNumber);
		if (categoryId > 0) {
			return pageHelper.fetchPage(getJdbcTemplate(),
					COUNT_LIST_REPLIED_QUESTION_BY_CATEGORY + categoryId,
					LIST_REPLIED_QUESTION_BY_CATEGORY + " order by "
							+ F_QUESTION_ALIAS + "." + sortColumn + " "
							+ sortOrder, new Object[] { categoryId,
							"%" + refNumber + "%" }, pageNo, recordSize,
					sortColumn, sortOrder, mapper);
		} else {
			return pageHelper.fetchPage(getJdbcTemplate(),
					COUNT_LIST_REPLIED_QUESTION, LIST_REPLIED_QUESTION
							+ " order by " + F_QUESTION_ALIAS + "."
							+ sortColumn + " " + sortOrder, new Object[] { "%"
							+ refNumber + "%" }, pageNo, recordSize,
					sortColumn, sortOrder, mapper);
		}
	}

	@Override
	public Page<Question> listUnansweredQuestions(int categoryId,
			String refNumber, int pageNo, int recordSize, String sortColumn,
			String sortOrder) {
		PaginationHelper<Question> pageHelper = new PaginationHelper<Question>();
		ParameterizedRowMapper<Question> mapper = new QuestionRowMapper(true);

		if (categoryId > 0) {
			return pageHelper.fetchPage(getJdbcTemplate(),
					COUNT_LIST_UNANSWERED_QUESTION_BY_CATEGORY + categoryId,
					LIST_UNANSWERED_QUESTION_BY_CATEGORY + " order by "
							+ F_QUESTION_ALIAS + "." + sortColumn + " "
							+ sortOrder, new Object[] { categoryId,
							"%" + refNumber + "%" }, pageNo, recordSize,
					sortColumn, sortOrder, mapper);
		} else {
			return pageHelper.fetchPage(getJdbcTemplate(),
					COUNT_LIST_UNANSWERED_QUESTION, LIST_UNANSWERED_QUESTION
							+ " order by " + F_QUESTION_ALIAS + "."
							+ sortColumn + " " + sortOrder, new Object[] { "%"
							+ refNumber + "%" }, pageNo, recordSize,
					sortColumn, sortOrder, mapper);
		}
	}

	private static class QuestionRowMapper implements
			ParameterizedRowMapper<Question> {

		private boolean cutString = false;

		public QuestionRowMapper() {
			this.cutString = false;
		}

		public QuestionRowMapper(boolean cutString) {
			this.cutString = cutString;
		}

		@Override
		public Question mapRow(ResultSet result, int index) throws SQLException {
			Question question = new Question();
			question.setId(result.getInt(F_QUESTION_ID));
			question.setEmail(result.getString(F_EMAIL));
			Category cat = new Category();
			cat.setId(result.getInt(F_CATEGORY_ID));

			String catName = result.getString(F_CATEGORY_NAME);
			cat.setNameFull(catName);
			if (cutString) {
				if (catName != null && catName.length() > 50) {
					catName = catName.substring(0, 50).concat("...");
				}
			}
			cat.setName(catName);

			question.setCategory(cat);
			question.setAnswer(result.getInt("HAS_ANSWER") > 0 ? true : false);
			question.setTag(result.getString(F_TAG));
			question.setConfidential(result.getBoolean(F_CONFIDENTIAL));
			question.setOrderNumber(result.getString(F_ORDER_NUMBER));

			String questionText = result.getString(F_QUESTION);
			question.setQuestionFull(questionText);
			if (cutString) {
				if (questionText != null && questionText.length() > 50) {
					questionText = questionText.substring(0, 50).concat("...");
				}
			}
			question.setQuestion(questionText);

			question.setFirstName(result.getString(F_FIRST_NAME));
			question.setLastName(result.getString(F_LAST_NAME));
			question.setCreated(result.getTimestamp(F_CREATED));
			question.setUpdated((result.getTimestamp(F_UPDATED)));
			question.setTopRank(result.getInt(F_TOP_RANK));
			question.setRefNumber(result.getString(F_REF_Num));
			question.setFaq_Question(result.getBoolean(F_IS_FAQ));
			return question;
		}
	}

	private static class FaqRowMapper implements ParameterizedRowMapper<Faq> {

		@Override
		public Faq mapRow(ResultSet result, int index) throws SQLException {
			Faq faq = new Faq();
			faq.setId(result.getInt(F_FAQ_ID));

			String questionText = result.getString(F_QUESTION);
			faq.setQuestionFull(questionText);
			if (questionText != null && questionText.length() > 50) {
				questionText = questionText.substring(0, 50).concat("...");
			}
			faq.setQuestion(questionText);

			String answerText = result.getString(F_ANSWER);
			faq.setAnswerFull(answerText);
			if (answerText != null && answerText.length() > 50) {
				answerText = answerText.substring(0, 50).concat("...");
			}
			faq.setAnswer(answerText);

			String tagText = result.getString(F_TAG);
			faq.setTagFull(tagText);
			if (tagText != null && tagText.length() > 50) {
				tagText = tagText.substring(0, 50).concat("...");
			}
			faq.setTag(tagText);

			faq.setCreated(result.getTimestamp(F_CREATED));
			faq.setUpdated(result.getTimestamp(F_UPDATED));
			return faq;
		}
	}

	private static class AnswerRowMapper implements
			ParameterizedRowMapper<Answer> {

		@Override
		public Answer mapRow(ResultSet result, int index) throws SQLException {
			Answer answer = new Answer();
			answer.setId(result.getInt(F_ANSWER_ID));
			answer.setQuestionId(result.getInt(F_QUESTION_ID));
			answer.setQuestionSubject(result.getString(F_QUESTION));
			answer.setRating(result.getInt(F_RATING));
			answer.setCreated(result.getTimestamp(F_ANSWER_ALIAS + "."
					+ F_CREATED));
			answer.setUpdated(result.getTimestamp(F_ANSWER_ALIAS + "."
					+ F_UPDATED));
			return answer;
		}
	}

	@Override
	public void rankQuestion(int questionId, int rank) {
		getJdbcTemplate().update(RANK_QUESTION,
				new Object[] { rank, questionId });
	}

	private static class CategoryRowMapper implements
			ParameterizedRowMapper<Category> {

		@Override
		public Category mapRow(ResultSet result, int index) throws SQLException {
			Category cat = new Category();
			cat.setId(result.getInt(F_CATEGORY_ID));

			String catName = result.getString(F_CATEGORY_NAME);
			if (catName != null && catName.length() > 50) {
				catName = catName.substring(0, 50).concat("...");
			}
			cat.setName(catName);
			return cat;
		}
	}

	@Override
	public Page<Category> listCategories(int pageNo, int recordSize,
			String sortColumn, String sortOrder) {
		PaginationHelper<Category> pageHelper = new PaginationHelper<Category>();
		ParameterizedRowMapper<Category> mapper = new CategoryRowMapper();
		return pageHelper.fetchPage(getJdbcTemplate(), COUNT_LIST_CATEGORY,
				LIST_CATEGORY + " order by " + sortColumn + " " + sortOrder,
				new Object[] {}, pageNo, recordSize, sortColumn, sortOrder,
				mapper);
	}

	@Override
	public void rateAnswer(int answerId, int rate) {
		getJdbcTemplate().update(RATE_ANSWER, new Object[] { rate, answerId });
	}
}